package com.msc.mc.util.data;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.compress.archivers.ArchiveOutputStream;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.msc.mc.util.ImportBean;
import com.msc.mc.util.ObjectBean;
import com.msc.mc.util.ObjectListBean;
import com.msc.mc.util.SimManagerImportBean;
import com.msc.sdm.ic.pub.SimMgrConnection;
import com.msc.sdm.ic.pub.SimMgrDbObject;
import com.msc.sdm.ic.pub.SimMgrDbObjectList;
import com.msc.sdm.ic.pub.SimMgrResultSet;

public class MatImport {
	public static void main( String[] args ) throws Exception {
		String path = "/data/mc_workspaces/SDMWeb/src/Material Name.xlsx";
		MatImport matImport = new MatImport( path );
		//matImport.generateImportStructXml( "/data/"  );
		matImport.parseImportFile();
		System.out.println( matImport.importData.toJSONString() );
	}
	
	private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    private static FormulaEvaluator evaluator;
    
    private String importFilePath ;
    
    private JSONArray importData;
    
    private JSONObject matClassification;
    
    private JSONObject matSpecMap ;
    
    private JSONObject existMaterials;
    
    private SimMgrConnection conn;
    
    private StringBuilder invalidMats;
    
    private StringBuilder logMessage ;
    private StringBuilder skipMessage;
    private StringBuilder migrationMessage;
    
    
    private final static  String NEWLINE_SEPARATOR= "\r\n" ;
    
    public void setMatClassification(JSONObject matClassification) {
		this.matClassification = matClassification;
	}
    
    public void setMatSpecification( JSONObject matSpecMap ) {
    	this.matSpecMap = matSpecMap ;
    }


	public MatImport( String mFilePath ) {
    	importFilePath = mFilePath ;
    	logMessage = new StringBuilder();
    	skipMessage = new StringBuilder() ;
    	migrationMessage = new StringBuilder();
    	invalidMats = new StringBuilder();
    	logMessage.append( "Import Material Summary - ").append( NEWLINE_SEPARATOR );
    }
	
	
	
	public void setConn(SimMgrConnection conn) {
		this.conn = conn;
	}

	public void setExistMaterials(JSONObject existMaterials) {
		this.existMaterials = existMaterials;
	}
	
	public String getLogMessage() {
		if( StringUtils.isNotBlank( migrationMessage.toString() ) ) {
			logMessage.append( "Migrate Material Summary:").append( NEWLINE_SEPARATOR ).append(  migrationMessage );
		}
		if( StringUtils.isNotBlank( skipMessage.toString() ) ) {
			logMessage.append( "Skip Material Summary:").append( NEWLINE_SEPARATOR ).append(  skipMessage );
		}
		return logMessage.toString() ;
	}
	
	public String getInvalidMats() {
		return invalidMats.toString();
	}

	private void parseImportFile()throws Exception {
    	Workbook wb = initWorkBook( importFilePath );
    	importData = new JSONArray();
    	org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);

    	int rowLen = sheet.getPhysicalNumberOfRows();
    	for( int r = 1 ; r < rowLen ; r++ ) {
    		Row row = sheet.getRow( r );
    		if(  row.getCell( 0 ) == null &&  row.getCell( 1 ) == null &&  row.getCell( 2 ) == null ) {
    			break;
    		}
    		
    	
    		JSONObject jsonObj = new JSONObject();
    		//PLM UID
			jsonObj.put( "plmUid" , getPlmUid( row  ) );
			//Material Name
			String matName = getMatName( row );
			
			if( StringUtils.isBlank( matName ) ) {
				break;
			}
			
        	jsonObj.put( "name" , matName );
        	String matSymbol = getMatSymbol( row )  ;
        	jsonObj.put( "matSymbol" , matSymbol );
        	String specSuffix = getSpecSuffix( row ) ;
        	jsonObj.put( "specSuffix" ,  specSuffix );
        	jsonObj.put( "matId" , getCAEMaterialId( row ) );
        	//color
        	jsonObj.put( "pmlColor" , getPlmColor( row ) );
        	//density
    		jsonObj.put( "plmDensity" , getPlmDensity( row ) );
    		//classification
        	String classification =  getClassificationPath( row ) ;
    		jsonObj.put( "classification" , classification ) ;
    		
    		JSONObject classJsonObj = matClassification.getJSONObject( classification );
    		//System.out.println( classification );
			if( classJsonObj == null ) {
				skipMessage.append( " Line " + (r+1) + " [input error]: classification not exist!" ).append( NEWLINE_SEPARATOR );
				continue;
			} 
    		
        	//if( StringUtils.isNotBlank( classification ) ) {
        	//	jsonObj.put( "materialIdKey" , "K" + classification.substring( 0 , 1 ) );
        	//}
        	
        	//specification
    		String specId = getSpecId( row ) ;
        	jsonObj.put( "specId" ,  specId );
        	String specName = getSpec( row );
        	jsonObj.put( "spec" , specName );
        	
        	String migration =  getMigration( row );
        	
        	
        	if( StringUtils.isNotBlank( matName ) && StringUtils.isNotBlank( matSymbol ) && StringUtils.isNotBlank( classification ) && 
        	  ( StringUtils.isNotBlank( specId ) || StringUtils.isNotBlank( specName ) )  ) {
        		
        		if( "Y".equals( migration ) ) {
        			
        			if( existMaterials != null && !existMaterials.containsKey( matName ) ) {
		        		skipMessage.append( " Line " + (r+1) + " [ material not exist ]:" ).append( matName).append( NEWLINE_SEPARATOR );
		        	} else {
		        		migrationMessage.append( " " ).append( matName ).append( NEWLINE_SEPARATOR );
		        		jsonObj.put( "objectId" , existMaterials.get( matName ) );
		        		importData.add( jsonObj );
		        	}
        			
        		}else if( "N".equals( migration )  ){
        			if( existMaterials != null && !existMaterials.containsKey( matName ) ) {
		        		skipMessage.append( " Line " + (r+1) + " [ material not exist ]:" ).append( matName).append( NEWLINE_SEPARATOR );
		        	} else {
		        		if( invalidMats.length() > 0 ) {
		        			invalidMats.append( "," );
		        		}
		        		invalidMats.append("'").append( existMaterials.get( matName ) ).append("'");
		        	}
        		}else {
        			
		        	if( existMaterials != null && existMaterials.containsKey( matName ) ) {
		        		skipMessage.append( " Line " + (r+1) + " [exist]:" ).append( matName).append( NEWLINE_SEPARATOR );
		        	}else if( !matName.equals( matSymbol ) && StringUtils.isBlank( specId ) && StringUtils.isNotBlank( specName ) ) {
		        		
		        		String simpleSpecCode = specName.startsWith( "NIO-SM" ) ? specName.substring( 7 ) : specName ;
		        		
		        		String newMatName = matSymbol;
		        		if(  StringUtils.isNotBlank( specSuffix ) ){
		        			newMatName = matSymbol + "_" + simpleSpecCode + "-" + specSuffix ;
						}else {
							newMatName = matSymbol + "_" + simpleSpecCode  ; 
						}
		        		if( newMatName.equals( matName ) ) {
		        			logMessage.append( " " ).append( matName ).append( NEWLINE_SEPARATOR );
			        		importData.add( jsonObj );
		        		}else {
		        			skipMessage.append( " Line " + (r+1) + " [input error]:" ).append( matName).append( NEWLINE_SEPARATOR );
		        		}
		        	}else {
		        		logMessage.append( " " ).append( matName ).append( NEWLINE_SEPARATOR );
		        		importData.add( jsonObj );
		        	}
        		}
        	}else {
        		skipMessage.append( " Line " + (r+1) + " [input error]:" ).append( matName).append( NEWLINE_SEPARATOR );
        	}
        //	Measures : Created
        	
    	}
    }
    
    //--------------------------------------------------------------------------
    private long timeSeq = 0 ;
    public String generateImportStructXml( String workDir   ) throws Exception {
    	
    	if( importData == null ) {
    		parseImportFile();
    	}
    	
    	SimManagerImportBean importBean = new SimManagerImportBean();
		importBean.addParam("canEditDuringImport", "false");
		importBean.addParam("useOidDuringImport", "true");
		
		ObjectListBean matSchemal = importBean.addObjectList( "MatMaterialsSchema" , "[name == 'NIOMaterial']", true );
		
		ObjectListBean matSpecBean  = importBean.addObjectList( "MatPropertyDefReference" , "[name == 'matSpec']", true );
		ObjectListBean matClassBean = importBean.addObjectList( "MatPropertyDefReference" , "[name == 'matClassification']", true );
		
		//---------------------------------
		ObjectBean dsItem = importBean.addObject( "Item" );
		dsItem.addAttributeBean( "name" , getUniqueName( "DS" ) , "trueAndAutoAdjust" );
		
		ObjectBean dsVariant = importBean.addObject( "Variant" );
		dsVariant.addAttributeBean( "item" , dsItem.getId() , "false" );
		dsVariant.addAttributeBean( "name" , "V1" , "false" );
		
		ObjectBean dsBean = importBean.addObject( "MatDataSource" );
		dsBean.addAttributeBean( "name", getUniqueName( "DS" ) , "false" ); 
		dsBean.addAttributeBean( "schema", matSchemal.getId() , "false" );
		dsBean.addAttributeBean( "variant", dsVariant.getId() , "false" );
		//---------------------------------

//System.out.println( "--->" + importData );
		
		
		Map< String , ObjectListBean > classObjListMap = new HashMap< String , ObjectListBean >();
		Map< String , ObjectListBean > specObjListMap = new HashMap< String , ObjectListBean >();

		for( int i = 0 ; i < importData.size() ; i++ ) {
			JSONObject jsonObj = importData.getJSONObject( i );
//System.out.println( jsonObj );			
			String matName =  jsonObj.getString( "name" );
			
			ObjectBean designDataItem = importBean.addObject( "Item" );
			designDataItem.addAttributeBean( "name" , getUniqueName( matName )  ,  "trueAndAutoAdjust" );
			
			ObjectBean designDataVariant = importBean.addObject( "Variant" );
			designDataVariant.addAttributeBean( "item" , designDataItem.getId() ,  "false" );
			designDataVariant.addAttributeBean( "name" , "V1",  "false" );
			
			List< ImportBean > refBeans = new ArrayList< ImportBean >();
			
			String specCondition = null;
			String specId = jsonObj.getString( "specId" );
			String spec = jsonObj.getString( "spec" );
			if( StringUtils.isNotBlank( specId ) ) {
				specCondition = "[plmUid=='" + specId + "']" ;
				spec = matSpecMap.getString( specId );
			}else if( StringUtils.isNotBlank( spec ) ) {
				specCondition = "[specCode=='" + spec + "'].activeRevision()";
			}
			
			if( StringUtils.isNotBlank( specCondition ) ) {
				
				ObjectListBean olB = specObjListMap.get(  specCondition );
				if( olB == null ) {
					olB = importBean.addObjectList( "NIOMatSpecification"  , specCondition , true );
					specObjListMap.put( specCondition , olB );
				}
				//jsonObj.put( "spec_ref" , olB.getId() );
				ObjectBean specBean = importBean.addObject( "MatReferenceProperty" );
				specBean.addAttributeBean( "matPropertyDef", matSpecBean , "false" );
				specBean.addAttributeBean( "name", "matSpec" , "false" );
				specBean.addAttributeBean( "value", olB , "false" );
				specBean.addAttributeBean( "variant", designDataVariant , "false" );
				refBeans.add( specBean );
			}
			
			String classification = jsonObj.getString( "classification" );
			
			JSONObject classJsonObj = matClassification.getJSONObject( classification );
//System.out.println( classification );
			String classOid = classJsonObj.getString( "oid" );
			
			ObjectListBean olBClass  = classObjListMap.get( classOid );
			if( olBClass == null  ) {
			   olBClass  = importBean.addObjectList( "NIOMatClassification" , "[objectId=='" + classOid + "']", true );
			   classObjListMap.put( classOid , olBClass );
			}
			//jsonObj.put( "classification_ref" , olB.getId() );
		
			ObjectBean classBean = importBean.addObject( "MatReferenceProperty" );
			classBean.addAttributeBean( "matPropertyDef", matClassBean , "false" );
			classBean.addAttributeBean( "name", "matClassification" , "false" );
			classBean.addAttributeBean( "value", olBClass , "false" );
			classBean.addAttributeBean( "variant", designDataVariant , "false" );
			refBeans.add( classBean );
 
			
			//migration
			String matObjectId = jsonObj.getString( "objectId" );
			ObjectBean designDataBean = null;
			if( StringUtils.isNotBlank( matObjectId ) ) {
				designDataBean = importBean.addObject( "NIOMaterial" , matObjectId , "true" , "false"  );
				
				SimMgrResultSet simMgrResultSet = conn.retrieveData( "NIOMaterial" , "[objectId=='" + matObjectId + "']" , "variant,generatedFromModel,matRevisableProperties" );
				 
				
				SimMgrDbObject variant = simMgrResultSet.getValue(0).getDbObject();
				ObjectListBean variantOLB = importBean.addObjectList( variant.getObjectType() , "[objectId== '"+ variant.getId() +"']", true );	
				 
				List< ImportBean > gfmBeans = new ArrayList< ImportBean >();
				SimMgrDbObjectList models = simMgrResultSet.getValue(1).getDbObjectList();
				for( int xi = 0 ; xi < models.size() ; xi++ ){
				  ObjectListBean modelOLB = importBean.addObjectList( models.get( xi ).getObjectType() , "[objectId== '"+ models.get( xi ).getId() +"']", true );
				  gfmBeans.add( modelOLB );
				}
				
				List< ImportBean > propBeans = new ArrayList< ImportBean >();
				if( refBeans.size() > 1 ) {
					propBeans.add( refBeans.get( 0 ) );
				}
				
				SimMgrDbObjectList properties = simMgrResultSet.getValue(2).getDbObjectList();
				for( int xi = 0 ; xi < properties.size() ; xi++ ){
					
					String pname = properties.get( xi ).evaluate("name" ).getString();
					if(  !"matSpec".equals( pname ) ) { 
						ObjectListBean propOLB = importBean.addObjectList( properties.get( xi ).getObjectType() , "[objectId== '"+ properties.get( xi ).getId() +"']", true );
						propBeans.add( propOLB );
					}
				}
				
				designDataBean.addAttributeBean("variant" , variantOLB.getId() , "false" );
				designDataBean.addAttributeBean("generatedFromModel",  gfmBeans.toArray( new ImportBean[gfmBeans.size()] )   , "false" );
				designDataBean.addAttributeBean("matRevisableProperties", propBeans.toArray( new ImportBean[propBeans.size()]  )  , "false" );//TODO
				
			}else {
				designDataBean = importBean.addObject( "NIOMaterial" );
				designDataBean.addAttributeBean("variant" , designDataVariant.getId() , "false" );
				designDataBean.addAttributeBean("matRevisableProperties", refBeans.toArray( new ImportBean[refBeans.size()]  )  , "false" );//TODO
				designDataBean.addAttributeBean("generatedFromModel",  dsBean   , "false" );
			}
			
			designDataBean.addAttributeBean("name", matName , "false" );
			designDataBean.addAttributeBean( "desig" , matName, "false" );
			designDataBean.addAttributeBean("matSpecNumber",spec, "false" );
			//designDataBean.addAttributeBean("description","New Material", "false" );
			designDataBean.addAttributeBean("materialGroup", classJsonObj.getString( "group" ) , "false" );
			designDataBean.addAttributeBean("materialSubgroup",classJsonObj.getString( "subgroup" ), "false" );
			if( StringUtils.isNotBlank(  classJsonObj.getString( "category" ) ) ) {
				designDataBean.addAttributeBean("materialCategory",classJsonObj.getString( "category" ), "false" );
			}
			
			if( StringUtils.isNotBlank( jsonObj.getString( "matSymbol" ) ) ) {
				designDataBean.addAttributeBean("materialSymbol",jsonObj.getString( "matSymbol" ), "false" );
			}
			if( StringUtils.isNotBlank( jsonObj.getString( "specSuffix" ) ) ) {
				designDataBean.addAttributeBean("specSuffix",jsonObj.getString( "specSuffix" ), "false" );
			}
			
			String matId = jsonObj.getString( "matId" );
			if( StringUtils.isNotBlank( matId ) ) {
				designDataBean.addAttributeBean( "matId" ,  matId   , "false" );
			}
			
			String plmUid = jsonObj.getString( "plmUid" );
			if( StringUtils.isNotBlank( plmUid ) ) {
				designDataBean.addAttributeBean("plmUid", plmUid , "false" );
			}
			
			String plmDensity = jsonObj.getString( "plmDensity" );
			if( plmDensity != null ) {
				designDataBean.addMeasureBean( "plm_density" , "Nominal" , plmDensity , "4", "Density", "g_per_cm__3" );
			}
			String color = jsonObj.getString( "plmColor" );
			if( StringUtils.isNotBlank( color ) ) {
				designDataBean.addAttributeBean( "plm_color" ,  color   , "false" );
			}
			
			/*String materialIdKey = jsonObj.getString( "materialIdKey" );
			if( StringUtils.isNotBlank( materialIdKey ) ) {
				String matId = MaterialSeqKey.getNextSeq( materialIdKey );
				designDataBean.addAttributeBean( "matId" ,  matId   , "false" );
			}*/
		}
		
		//String message = importBean.getXMLString();
		File xmlFile   =  importBean.getXMLFile( workDir , "structure.xml"  );
		
		String zipFilePath = workDir + "MaterialEditor.zip" ;
		FileOutputStream fos=new java.io.FileOutputStream(zipFilePath); 
		ArchiveOutputStream zaos = new org.apache.commons.compress.archivers.ArchiveStreamFactory().createArchiveOutputStream("zip",new java.io.FileOutputStream(zipFilePath) );
		 
		ZipArchiveEntry zipArchiveEntry = new org.apache.commons.compress.archivers.zip.ZipArchiveEntry( xmlFile.getName() );    
		zaos.putArchiveEntry(zipArchiveEntry);     
		FileInputStream fis = new java.io.FileInputStream( xmlFile );
		org.apache.commons.compress.utils.IOUtils.copy( fis , zaos);     
		zaos.closeArchiveEntry();    
		fis.close(); 
		zaos.finish();
		fos.close();
		return zipFilePath ;
    }
    
    public  String getUniqueName( String prefix ) {
		//return prefix + CoreWidgetsUtils.generateUnqiueWidgetName();
		return prefix + Long.toHexString( timeSeq++ ) ;
	}
    
    
    private String getPlmUid( Row row ) {
    	Cell cell = row.getCell( 0 );
    	String matId = null ;
    	if( cell != null ) {
    		cell.setCellType( 1 );
    		matId =  cell.getStringCellValue();
    	}
    	return matId;
    }
    
    private String getMatName( Row row ) {
		String name1 = null  , name2 = null;
//    	Cell cell1 = row.getCell( 1 );
//    	if( cell1 != null ) {
//    		cell1.setCellType( 1 );
//    		name1 = cell1.getStringCellValue() ;
//    	}
    	
		Cell cell2 = row.getCell( 2 );
		if( cell2 != null ) {
			cell2.setCellType( 1 );
			name2 = cell2.getStringCellValue() ;
		}
		return StringUtils.isBlank( name1 ) ? name2 : name1 ;
    }
    
    private String getMatSymbol( Row row ) {
    	Cell cell = row.getCell( 3 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    private String getSpecSuffix( Row row ) {
    	Cell cell = row.getCell( 4 );
    	String res = null ;
    	if( cell != null ) {
    		res =  getCellValueByCell( cell );
    	}
    	return res;
    }
    
    private String getCAEMaterialId( Row row ) {
    	Cell cell = row.getCell( 5 );
    	String res = null ;
    	if( cell != null ) {
    		res =  getCellValueByCell( cell );
    	}
    	return res;
    }
    
    private String getPlmColor( Row row ) {
    	Cell cell = row.getCell( 6 );
    	String color = null ;
    	if( cell != null ) {
	    	 color =  cell.getStringCellValue();
    	}
    	return color;
    }
    
    private double getPlmDensity( Row row ) {
    	Cell cell = row.getCell( 7 );
    	String str =  cell.getStringCellValue();
    	double density = 0 ;
//    	if( str.endsWith( "KG__M3" ) ) {
//    		density = Double.parseDouble( str.substring( 0 , str.length() -  6 ) ) ;
//    	}
    	if( str.endsWith( "G_CM3") ) {
    		density = Double.parseDouble( str.substring( 0 , str.length() -  5 ) ) ;
    	}
    	return density;
    }
    
    private String getClassificationPath( Row row ) {
    	Cell cell = row.getCell( 8 );
    	String classification = null ;
    	if( cell != null ) {
    		classification =  cell.getStringCellValue();
    	}
    	return classification;
    }
    

    private String getSpecId( Row row ) {
    	Cell cell = row.getCell( 9 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    
    private String getSpec( Row row ) {
    	Cell cell = row.getCell( 10 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    private String getMigration( Row row ) {
    	Cell cell = row.getCell( 11 );
    	String res = null ;
    	if( cell != null ) {
    		res =  cell.getStringCellValue();
    	}
    	return res;
    }
    
    
    
    private  String getCellValueByCell(Cell cell) {
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        if(cellType==Cell.CELL_TYPE_FORMULA){  
            cellType=evaluator.evaluate(cell).getCellType();
        }
         
        switch (cellType) {
        case Cell.CELL_TYPE_STRING:  
            cellValue= cell.getStringCellValue().trim();
            cellValue=StringUtils.isEmpty(cellValue) ? "" : cellValue; 
            break;
        case Cell.CELL_TYPE_BOOLEAN:   
            cellValue = String.valueOf(cell.getBooleanCellValue()); 
            break; 
        case Cell.CELL_TYPE_NUMERIC:  
             if (HSSFDateUtil.isCellDateFormatted(cell)) {   
            	 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                 cellValue = sdf.format( cell.getDateCellValue() );
             } else {   
                 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 
             } 
            break;
        default:  
            cellValue = "";
            break;
        }
        return cellValue;
    }
    
    /**
    *  
    * @return
    * @throws IOException
    */
   public  Workbook initWorkBook( String mFilePath ) throws IOException {
        File file = new File(mFilePath);
        InputStream is = new FileInputStream(file);

        Workbook workbook = null;
        if (mFilePath.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(is);
        } else if (mFilePath.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(is);
        }
        
        evaluator= workbook.getCreationHelper().createFormulaEvaluator();
        
        return workbook;
    }
  
	
}
